﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using LuZhouLaoJiao;
using MySql.Data.MySqlClient;
using System.IO;
//using Excel = Microsoft.Office.Interop.Excel;

namespace LuZhouLaoJiao
{
    public partial class RemoteBatchInfo : Form
    {
        public RemoteBatchInfo()
        {
            InitializeComponent();
        }

        int pageSize = Global.pageSize;
        int totalRows = 0;
        int pageCount = 0; //totalRows/pageSize
        int pageCurrent = 1;
        int RowCurrent = 0;

        public Boolean isAddRow = false;
        public string doublePiCi = null;
        public int doubleRow;
        public float avgAcidity = 0;
        public DataTable dt = null;
        MySqlConnection conn = null;
        MySqlCommand comm = null;
        MySqlDataReader reader = null;
        string strCon = null;
        string dbUser = Global.getRegeditValue("localDBUser");
        string dbPwd = Global.getRegeditValue("localDBPwd");
        string database = Global.getRegeditValue("localDatabase");
        string ip = Global.getRegeditValue("localIP");
        
        private void RemoteBatchInfo_Load(object sender, EventArgs e)
        {
            pageSize = (this.Height - 80) / dataGridView1.RowTemplate.Height;
            //dataGridView1.Height = pageSize * dataGridView1.RowTemplate.Height;
            lbLoginer.Text = "User: " + Global.userName;
            LoadData(pageCurrent, RowCurrent);
        }

        private void RemoteBatchInfo_SizeChanged(object sender, System.EventArgs e)
        {
            pageSize = (this.Height - 80) / dataGridView1.RowTemplate.Height;
            pageCount = (int)Math.Ceiling((double)(totalRows + 0.1) / pageSize);
            dataGridView1.Height = pageSize * dataGridView1.RowTemplate.Height;
            if (pageCount < pageCurrent)
            {
                RowCurrent = 0;
                pageCurrent = 1;
            }
            LoadData(pageCurrent, RowCurrent);
        }

        public void LoadData(int pCurrent, int rCurrent)
        {
            strCon = "server = " + ip + "; user id = " + dbUser + "; password = " + dbPwd + "; database = " + database + "; persist security info = false";
            conn = new MySqlConnection(strCon);
            comm = new MySqlCommand();
            comm.Connection = conn;
            comm.CommandText = "select count(*) from batch_info;";
            try
            {
                conn.Open();
                totalRows = Convert.ToInt32(comm.ExecuteScalar());
                pageCount = (int)Math.Ceiling((double)(totalRows + 0.1) / pageSize);
            }
            catch (System.Exception ex)
            {
                if (conn.State != ConnectionState.Open)
                {
                    MessageBox.Show("无法连接到远程服务器，请重新设置连接参数");
                    this.Visible = false;
                    SetLocalDatabase sld = new SetLocalDatabase();
                    sld.ShowDialog();
                    this.Close();
                    return;
                }
                else
                {
                    MessageBox.Show("数据库读取错误(BI1)：" + ex.Message);
                }
                
            }
            comm.CommandText = "select * from batch_info order by pici_id desc limit " + RowCurrent + "," + pageSize + ";";
            lbPageInfo.Text = "共" + pageCount + "页， 当前第" + pageCurrent + "页";
            try
            {

                reader = comm.ExecuteReader();
                dt = new DataTable();
                dt.Load(reader);

                dt.Columns[0].ColumnName = "批次号";
                dt.Columns[1].ColumnName = "平均酸度";
                dt.Columns[2].ColumnName = "检测人";
                dt.Columns[3].ColumnName = "样本来源";
                dt.Columns[4].ColumnName = "检测日期";
                dt.Columns[5].ColumnName = "检测时间";
                dt.Columns[6].ColumnName = "备注";

                dataGridView1.DataSource = dt;
                dataGridView1.Columns[0].ReadOnly = true;
                dataGridView1.Columns[0].Width = 120;
                dataGridView1.Columns[1].ReadOnly = true;
                dataGridView1.Columns[2].ReadOnly = true;
                dataGridView1.Columns[4].ReadOnly = true;
                dataGridView1.Columns[5].ReadOnly = true;

                //this.Width = 780;
                
            }
            catch (System.Exception ex)
            {
                if (!ex.GetType().ToString().Contains("ArgumentOutOfRangeException"))
                {
                    MessageBox.Show("数据库读取错误(BI1)：" + ex.Message);
                }
            }
            finally
            {
            }
        }


        //当单元格退出编辑模式，如果修改了单元格值.
        private void dataGridView1_CellParsing(object sender, DataGridViewCellParsingEventArgs e)
        {
            string header = dataGridView1.Columns[e.ColumnIndex].HeaderText;
            comm.CommandText = "update batch_info set " + Global.headerMatch(header) + " = '" + e.Value.ToString() + "' where pici_id = '" + dataGridView1[0, e.RowIndex].Value + "';";
            try
            {
                comm.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("数据库更新值错误：" + ex.Message);
            }
        }

        private void BatchInfo_FormClosed(Object sender, FormClosedEventArgs e)
        {
            if (conn.State == ConnectionState.Open)
            {
                reader.Close();
                conn.Close();
            }
        }
        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            Global.inquiried = false;
            if (e.ColumnIndex == 0)
            {
                    doublePiCi = dataGridView1[0, e.RowIndex].Value.ToString();
                    RemoteBatchDetailsInfo batchDetails = new RemoteBatchDetailsInfo();
                    batchDetails.ShowDialog(this);
            }
            
        }

        private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            if (dataGridView1[1, e.RowIndex].Value.ToString() == "")
            {
                MessageBox.Show("酸度值不能修改为空");
                return;
            }
            comm.CommandText = "update batch_info set suandu = '" + avgAcidity + "' where pici_id = '" + doublePiCi + "';";
            try
            {
                comm.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("无法写入平均酸度(BI)：" + ex.Message);
            }     
        }

        //private void 本地数据库设置ToolStripMenuItem_Click(object sender, EventArgs e)
        //{
        //    SetLocalDatabase setLocal = new SetLocalDatabase();
        //    setLocal.ShowDialog();
        //}

        //private void 远程数据库设置ToolStripMenuItem_Click(object sender, EventArgs e)
        //{
        //    SetRemoteDB setRemote = new SetRemoteDB();
        //    setRemote.ShowDialog();
        //}

        private void 输出到ExcelToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                DialogResult result = MessageBox.Show("确定要将本页数据输出到Excel?", "输出数据到Excel", MessageBoxButtons.YesNo);
                if (result == DialogResult.Yes)
                {
                    Global.DataGridviewShowToExcel(dataGridView1, true);
                }
            }
            catch(System.Runtime.InteropServices.COMException)
            {
                MessageBox.Show("本机上没有Windows Office应用程序，无法使用此功能.");
            }
            
            
        }

        //private bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)
        //{
        //    if (dgv.Rows.Count == 0)
        //        return false;
        //    //建立Excel对象   
        //    Excel.Application excel = new Excel.Application();
        //    Excel.Workbook wb = excel.Application.Workbooks.Add(true);
        //    excel.Visible = isShowExcle;
        //    //生成字段名称
        //    for (int i = 0; i < dgv.ColumnCount; i++)
        //    {
        //        excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
        //    }
        //    //填充数据.
        //    for (int i = 0; i < dgv.RowCount - 1; i++)
        //    {
        //        for (int j = 0; j < dgv.ColumnCount; j++)
        //        {
        //            if (dgv[j, i].ValueType == typeof(string))
        //            {
        //                excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
        //            }
        //            else
        //            {
        //                excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
        //            }
        //        }
        //    }
        //    return true;
        //}

        private void dataGridView1_CellToolTipTextNeeded(object sender, DataGridViewCellToolTipTextNeededEventArgs e)
        {
            if (e.ColumnIndex == 0 && e.RowIndex >= 0)
            {
                if (dataGridView1[1, e.RowIndex].Value.ToString() != "")
                {
                    e.ToolTipText = "双击查看详细信息";
                }
                //else
                //{
                //    e.ToolTipText = "双击进行样品检测";
                //}   
            }
        }

        //private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        //{

        //}

        private void 查询批次ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            RemoteInquiryInput search = new RemoteInquiryInput();
            search.ShowDialog(this);
        }

        //private void 创建管理员账户ToolStripMenuItem_Click(object sender, EventArgs e)
        //{
        //    if (isAdmin())
        //    {
        //        CreateAdminUser admin = new CreateAdminUser();
        //        admin.ShowDialog();
        //    }
        //    else
        //    {
        //        MessageBox.Show("只有管理员账户才能创建账户.");
        //    }
        //}

        //private void 创建普通用户ToolStripMenuItem_Click(object sender, EventArgs e)
        //{
        //    if (isAdmin())
        //    {
        //        CreateNormalUser user = new CreateNormalUser();
        //        user.ShowDialog();
        //    }
        //    else
        //    {
        //        MessageBox.Show("只有管理员账户才能创建账户.");
        //    }
        //}

        //private void 删除账户ToolStripMenuItem_Click(object sender, EventArgs e)
        //{


        //    if (isAdmin())
        //    {
        //        DeleteUser deleteUser = new DeleteUser();
        //        deleteUser.ShowDialog();
        //    }
        //    else
        //    {
        //        MessageBox.Show("只有管理员账户才能删除其他账户.");
        //    }
        //}

        private bool isAdmin()
        {
            bool admin = false;
            comm.CommandText = "select * from user where name = '" + Global.userName + "' and isadmin = '1';";
            try
            {
                int i = Convert.ToInt32(comm.ExecuteScalar());
                if (i > 0)
                {
                    admin = true;
                }
                else
                {
                    admin = false;
                }
                return admin;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("数据库读取错误(BI)：" + ex.Message);
                return false;
            }
            finally
            {
                //conn.Close();
            }
        }

        private void lbFirstPage_Click(object sender, EventArgs e)
        {
            if (pageCurrent == 1)
            {
                MessageBox.Show("已经是第一页.");
                return;
            }
            pageCurrent = 1;
            RowCurrent = 0;
            LoadData(pageCurrent, RowCurrent);
        }

        private void lbLastPage_Click(object sender, EventArgs e)
        {
            if (pageCurrent == 1)
            {
                MessageBox.Show("已经是第一页.");
                return;
            }
            pageCurrent--;
            RowCurrent = (pageCurrent - 1) * pageSize;

            LoadData(pageCurrent, RowCurrent);
        }

        private void lbNextPage_Click(object sender, EventArgs e)
        {
            if (pageCurrent == pageCount)
            {
                MessageBox.Show("已经到达最后一页.");
                return;
            }
            RowCurrent = pageCurrent * pageSize;
            pageCurrent++;
            LoadData(pageCurrent, RowCurrent);
        }

        private void lbFinalPage_Click(object sender, EventArgs e)
        {
            if (pageCurrent == pageCount)
            {
                MessageBox.Show("已经是最后一页.");
                return;
            }
            pageCurrent = pageCount;
            RowCurrent = (pageCurrent - 1) * pageSize;
            LoadData(pageCurrent, RowCurrent);
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            try
            {
                pageCurrent = int.Parse(textBox1.Text.ToString());
                if (pageCurrent > pageCount)
                {
                    MessageBox.Show("输入页数太大.");
                    return;
                }
                else if (pageCurrent < 1)
                {
                    MessageBox.Show("输入页数太小.");
                    return;
                }
                RowCurrent = (pageCurrent - 1) * pageSize;
                LoadData(pageCurrent, RowCurrent);
            }
            catch (System.FormatException ex)
            {
                if (textBox1.Text.ToString() == "")
                    return;
                MessageBox.Show("只接受整数值.");
            }
        }

        private void 删除批次ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (dataGridView1.CurrentRow == null)
            {
                MessageBox.Show("您还没有选中任何数据.");
                return;
            }
            if (dataGridView1.CurrentRow.Index < 0)
            {
                return;
            }
            //DeleteBatch deleteBatch = new DeleteBatch();
            //deleteBatch.ShowDialog();
            DialogResult result = MessageBox.Show("确定要批次" + dataGridView1[0, dataGridView1.CurrentRow.Index].Value.ToString() + "?", "删除批次", MessageBoxButtons.YesNo);
            if (result == DialogResult.Yes)
            {
                comm.CommandText = "delete from batch_info where pici_id = '" + dataGridView1[0, dataGridView1.CurrentRow.Index].Value.ToString() + "';";
                try
                {
                    int i = comm.ExecuteNonQuery();
                    if (i > 0)
                    {
                        MessageBox.Show("批次" + dataGridView1[0, dataGridView1.CurrentRow.Index].Value.ToString() + "已经被删除.");
                    }
                }
                catch (System.Exception ex)
                {
                    MessageBox.Show("删除批次出现错误(BI)" + ex.Message);
                }
                LoadData(pageCurrent, RowCurrent);
            }
        }

        private void 用户管理ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (isAdmin())
            {
                ManageUser mu = new ManageUser();
                mu.ShowDialog();
            }
            else
            {
                MessageBox.Show("只有管理员才能使用此功能.");
            }
        }
    }
}
